/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Functions;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import utils.SQLServerDB;

/**
 *
 * @author vanchung07
 */
public class FunctionsDAO {

    private String lastError;
    private final String SQL_CREATE = "INSERT INTO Functions Values(?, ?)";
    private final String SQL_READALL = "SELECT * FROM Functions";
    private final String SQL_READBYID = "SELECT * FROM Functions WHERE ID = ?";
    private final String SQL_UPDATE = "UPDATE Functions SET Name = ?, Description = ? WHERE ID = ?";
    private final String SQL_DELETE = "DELETE FROM Functions WHERE ID = ?";
    private Connection conn;

    public boolean create(Functions f) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CREATE);
            pr.setString(1, f.getName());
            pr.setString(2, f.getDescript());
            if (pr.executeUpdate() > 0) {
                setLastError("Create Successful!");
                return true;
            } else {
                setLastError("Error: Create Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public ArrayList<Functions> ReadAll() {
        conn = SQLServerDB.getConn();
        ArrayList<Functions> list = new ArrayList<>();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_READALL);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Functions r = new Functions();
                r.setId(rs.getInt("ID"));
                r.setName(rs.getString("Name"));
                r.setDescript(rs.getString("Description"));
                list.add(r);
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return list;
    }

    public Functions readById(int id) {
        conn = SQLServerDB.getConn();
        Functions f = new Functions();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_READBYID);
            pr.setInt(1, id);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                f.setId(rs.getInt("ID"));
                f.setName(rs.getString("Name"));
                f.setDescript(rs.getString("Description"));
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return f;
    }

    public boolean update(Functions f) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_UPDATE);
            pr.setString(1, f.getName());
            pr.setString(2, f.getDescript());
            pr.setInt(3, f.getId());
            if (pr.executeUpdate() > 0) {
                setLastError("Update Successful!");
                return true;
            } else {
                setLastError("Error: Update Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean delete(int id) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_DELETE);
            pr.setInt(1, id);
            if (pr.executeUpdate() > 0) {
                setLastError("Delete Successful!");
                return true;
            } else {
                setLastError("Error: Delete Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }

    public static void main(String[] args) {
//        Functions f = new Functions();
//        f.setId(1);
//        f.setName("Addd");
//        f.setDescript("Add a sitem");
        FunctionsDAO dao = new FunctionsDAO();
        System.out.println(dao.delete(2));
        System.out.println(dao.getLastError());
        ArrayList<Functions> list = dao.ReadAll();
        for (Functions functions : list) {
            System.out.println(functions.getId());
        }
    }
}
